/*
 * ForRentDAO.java
 *
 * Created on March 22, 2007, 1:45 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.group1E.data;

import edu.byu.isys413.group1E.Cache;
import edu.byu.isys413.group1E.ConnectionPool;
import edu.byu.isys413.group1E.ConnectionPoolException;
import edu.byu.isys413.group1E.DataException;
import edu.byu.isys413.group1E.GUID;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * @author Jon Tehero
 */
public class ForRentDAO extends PhysicalDAO{
    private static ForRentDAO instance = null;
    
//******************************************************************************
//   SINGLETON PATTERN
//******************************************************************************
    /** Using singleton pattern to call an instance of the ForRentDAO **/
    public ForRentDAO() {}
    /**Retrieves an instance of the RentDAO object**/
    public static synchronized ForRentDAO getInstance() {
        if (instance == null) {
            instance = new ForRentDAO();
        }
        return instance;
    }
    
//******************************************************************************
//   CREATE
//******************************************************************************
    /**
     * Creates a new Rental Business Object with a newly generated GUID.
     *
     * @return a newly created RentalBO with unique GUID
     * @throws java.lang.Exception to catch any errors in the creation process.
     */
    public ForRentBO create() throws Exception{
        String id = GUID.generate();
        ForRentBO bo = new ForRentBO(id);
        Cache c = Cache.getInstance();
        c.put(bo.getProductID(), bo);
        return bo;
    }
    
//******************************************************************************
//   READ - Incorporates 2 Read Methods (1 for external use, 1 for internal),
//          and 1 ReadAll Method
//******************************************************************************
    /**
     * Creates a new FeeBO object with a newly generated GUID.
     *
     * @return a newly created FeeBO with unique GUID
     * @throws java.lang.Exception to catch any errors in the creation process.
     */
    public ProductBO read(String id) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        ForRentBO forRent = (ForRentBO)c.get(id);
        if (forRent != null){
            return forRent;
        }
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            forRent = (ForRentBO)this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id = " + id, e);
        }catch (ConnectionPoolException x) {
            //rollback
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return forRent;
    }
    
    public synchronized ProductBO read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        ForRentBO forRent = (ForRentBO)c.get(id);
        if (forRent != null){
            return forRent;                     // if so, return it immediately
        }
        //put it to the cache so the super.read can get it
        c.put(forRent.getProductID(), forRent);
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        forRent = (ForRentBO)super.read(id, conn);
        
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM ForRent WHERE ProductID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            int temp;
            forRent.setTimesRented(rs.getInt("TIMES_RENTED"));
            temp = rs.getInt("CURRENT_RENTAL_ITEM");
            if(temp == 0){
                forRent.setCurrentRentalItem(false);
            } else{
                forRent.setCurrentRentalItem(true);
            }
            temp = rs.getInt("IN_STOCK");
            if(temp == 0){
                forRent.setInStock(false);
            } else{
                forRent.setInStock(true);
            }
            forRent.setAlreadyInDB(true);
            forRent.setDirty(false);
            
            // save to the cache
            c.put(forRent.getProductID(), forRent);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return forRent;
    }
    
    public synchronized ArrayList<ProductBO> readAll() throws DataException, SQLException, ConnectionPoolException{
        ArrayList<ProductBO> list = new ArrayList<ProductBO>();
        String query = "SELECT * FROM ForRent";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
//extract the elements from the result set and build a list of results
//build the object from the result set
        while (rs.next()) {
            ForRentBO forRent = new ForRentBO(rs.getString("ProductID"));
            Cache.getInstance().put(rs.getString("ProductID"), forRent);
            forRent = (ForRentBO)super.read(rs.getString("ProductID"), conn);
            
            int temp;
            forRent.setTimesRented(rs.getInt("TIMES_RENTED"));
            temp = rs.getInt("CURRENT_RENTAL_ITEM");
            if(temp == 0){
                forRent.setCurrentRentalItem(false);
            } else{
                forRent.setCurrentRentalItem(true);
            }
            temp = rs.getInt("IN_STOCK");
            if(temp == 0){
                forRent.setInStock(false);
            } else{
                forRent.setInStock(true);
            }
            forRent.setAlreadyInDB(true);
            forRent.setDirty(false);
            
// save to the list
            list.add(forRent);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return list;
    }
    
    public synchronized ArrayList<ProductBO> readAllRentableItems() throws DataException, SQLException, ConnectionPoolException{
        ArrayList<ProductBO> list = new ArrayList<ProductBO>();
        String query = "SELECT * FROM ForRent WHERE Current_Rental_Item = 1 AND In_Stock = 1";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        //build the object from the result set
        while (rs.next()) {
            ForRentBO forRent = new ForRentBO(rs.getString("ProductID"));
            Cache.getInstance().put(rs.getString("ProductID"), forRent);
            forRent = (ForRentBO)super.read(rs.getString("ProductID"), conn);
            forRent.setTimesRented(rs.getInt("TIMES_RENTED"));
            forRent.setCurrentRentalItem(true);
            forRent.setInStock(true);
            forRent.setAlreadyInDB(true);
            forRent.setDirty(false);
            // save to the list
            list.add(forRent);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return list;
    }
    public synchronized ArrayList<ForRentBO> readAllForRentObjects() throws DataException, SQLException, ConnectionPoolException{
                 
        Cache c = Cache.getInstance();
        String id;
        Connection conn = ConnectionPool.getInstance().get();
        String query = ("SELECT * FROM For_Rent WHERE in_stock = 1 AND Current_Rental_Item = 1 AND PRODUCTID IN (SELECT ProductID FROM PHYSICAL_PRODUCT WHERE CPRODUCTID IN " +
                "(SELECT PRODUCTID FROM CONCEPTUAL_PRODUCT WHERE FORRENT = 1))");
        //pstmt.setString(1,);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        ArrayList<ForRentBO> frList = new ArrayList<ForRentBO>();
        //build your list by the result set
        while(rs.next()) {
            ForRentBO forRent = new ForRentBO(rs.getString("ProductID"));
            forRent.setInStock(true);
            forRent.setTimesRented(rs.getInt("Times_Rented"));
            forRent.setCurrentRentalItem(true);
            c.put(forRent.getProductID(),forRent);
            forRent = (ForRentBO)super.read(forRent.getProductID(),conn);
            frList.add(forRent);
        }
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        // return the forRentBO
        return frList;
    }
    public synchronized ProductBO readBySKU(String xid) throws DataException, SQLException, ConnectionPoolException{
        String id;
        String query = "SELECT * FROM PRODUCT WHERE SKU = '" + xid + "'";
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        if(rs.next()) {
            id = rs.getString("ProductID");
        }else{
            throw new DataException("Object was not found in the database.");
        }
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        ForRentBO forRent = (ForRentBO)c.get(id);
        // If so, return it immediately
        if (forRent != null){
            return forRent;
        }
        //put it to the cache so the super.read can get it
        forRent = new ForRentBO(id);
        c.put(id, forRent);
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        forRent = (ForRentBO)super.read(id, conn);
        rs = stmt.executeQuery("SELECT * FROM For_Sale WHERE ProductID = '" + id + "'");
        conn.commit();
        //build the object from the result set
        if (rs.next()) {
            int temp;
            forRent.setTimesRented(rs.getInt("TIMES_RENTED"));
            temp = rs.getInt("CURRENT_RENTAL_ITEM");
            if(temp == 0){
                forRent.setCurrentRentalItem(false);
            } else{
                forRent.setCurrentRentalItem(true);
            }
            temp = rs.getInt("IN_STOCK");
            if(temp == 0){
                forRent.setInStock(false);
            } else{
                forRent.setInStock(true);
            }
            forRent.setAlreadyInDB(true);
            forRent.setDirty(false);
            // save to the cache
            c.put(forRent.getProductID(), forRent);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the forRentBO
        return forRent;
    }
    public synchronized ArrayList<ForRentBO> readByAvailableCat(String cat) throws DataException, SQLException, ConnectionPoolException{
        Cache c = Cache.getInstance();
        String id;
        Connection conn = ConnectionPool.getInstance().get();
        PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM For_Rent WHERE in_stock = 1 AND Current_Rental_Item = 1 AND PRODUCTID IN (SELECT ProductID FROM PHYSICAL_PRODUCT WHERE CPRODUCTID IN " +
                "(SELECT PRODUCTID FROM CONCEPTUAL_PRODUCT WHERE CATEGORY = ? AND FORRENT = 1))");
        pstmt.setString(1, cat);
        ResultSet rs = pstmt.executeQuery();
        conn.commit();
        ArrayList<ForRentBO> frList = new ArrayList<ForRentBO>();
        //build your list by the result set
        while(rs.next()) {
            ForRentBO forRent = new ForRentBO(rs.getString("ProductID"));
            forRent.setInStock(true);
            forRent.setTimesRented(rs.getInt("Times_Rented"));
            forRent.setCurrentRentalItem(true);
            c.put(forRent.getProductID(),forRent);
            forRent = (ForRentBO)super.read(forRent.getProductID(),conn);
            frList.add(forRent);
        }
        pstmt.close();
        ConnectionPool.getInstance().release(conn);
        // return the forRentBO
        return frList;
    }    
    /** searches for a rental item by category and keyword
     */
    public synchronized ArrayList<ForRentBO> lookupByKeyword(String category, String keyword) throws DataException, SQLException, ConnectionPoolException{
        Cache c = Cache.getInstance();
        String id;
        Connection conn = ConnectionPool.getInstance().get();
        PreparedStatement pstmt;
        if(!category.equals("All Categories")){
            pstmt = conn.prepareStatement("SELECT * FROM For_Rent WHERE in_stock = 1 AND Current_Rental_Item = 1 AND PRODUCTID IN (SELECT ProductID FROM PHYSICAL_PRODUCT WHERE CPRODUCTID IN " +
                    "(SELECT PRODUCTID FROM CONCEPTUAL_PRODUCT WHERE CATEGORY = ? AND FORRENT = 1 AND Description LIKE '%" + keyword + "%'))");
            pstmt.setString(1, category);
        }else{
            pstmt = conn.prepareStatement("SELECT * FROM For_Rent WHERE in_stock = 1 AND Current_Rental_Item = 1 AND PRODUCTID IN (SELECT ProductID FROM PHYSICAL_PRODUCT WHERE CPRODUCTID IN " +
                    "(SELECT PRODUCTID FROM CONCEPTUAL_PRODUCT WHERE FORRENT = 1 AND Description LIKE '%" + keyword + "%'))");
        }
        
        ResultSet rs = pstmt.executeQuery();
        conn.commit();
        ArrayList<ForRentBO> frList = new ArrayList<ForRentBO>();
        //build your list by the result set
        while(rs.next()) {
            ForRentBO forRent = new ForRentBO(rs.getString("ProductID"));
            forRent.setInStock(true);
            forRent.setTimesRented(rs.getInt("Times_Rented"));
            forRent.setCurrentRentalItem(true);
            c.put(forRent.getProductID(),forRent);
            forRent = (ForRentBO)super.read(forRent.getProductID(),conn);
            frList.add(forRent);
        }
        pstmt.close();
        ConnectionPool.getInstance().release(conn);
        // return the forRentBO
        return frList;
    }
    
//******************************************************************************
//   UPDATE - Incorporates 1 Insert, & 1 Update methods
//******************************************************************************
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     */
    synchronized void insert(ProductBO bo, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        //update the DB
        ForRentBO BO = (ForRentBO)bo;
        super.insertPhysical(BO.getProductID(), conn);
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO For_Rent (PRODUCTID, TIMES_RENTED, CURRENT_RENTAL_ITEM, IN_STOCK) VALUES (?, ?, ?, ?)");
        int temp;
        pstmt.setString(1, BO.getProductID());
        pstmt.setInt(2, BO.getTimesRented());
        if(BO.isCurrentRentalItem() == true){temp = 1;}else{temp = 0;} pstmt.setInt(3, temp);
        if(BO.isInStock() == true){temp = 1;}else{temp = 0;} pstmt.setInt(4, temp);
        pstmt.executeUpdate();
        pstmt.close();
        // tell the object that it's now in the db (so we call update next time not insert)
        BO.setAlreadyInDB(true);
    }//end insert
    protected void update(ProductBO bo, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        //update the DB
        ForRentBO BO = (ForRentBO)bo;
        super.updatePhysical(BO.getProductID(), conn);
        PreparedStatement pstmt = conn.prepareStatement("UPDATE For_Rent SET timesRented = ?, SET currentRental = ?, SET currentRentalItem = ? WHERE ProductID = ?");
        int temp;
        pstmt.setInt(1, BO.getTimesRented());
        if(BO.isCurrentRentalItem() == true){temp = 1;}else{temp = 0;} pstmt.setInt(2, temp);
        if(BO.isInStock() == true){temp = 1;}else{temp = 0;} pstmt.setInt(3, temp);
        pstmt.setString(4, BO.getProductID());
        pstmt.executeUpdate();
        pstmt.close();
        BO.setAlreadyInDB(true);
    }// end update
}
